﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class UserMovingDAL
    {
        public string Waming = "SqlServerDAL.UserMovingDAL";

        public bool Save(UserMovingDto dto)
        {
            try
            {
                var sql = "";
                var parameters = new List<SqlParameter>
                                     {
                                         new SqlParameter("@MovingUserId", SqlDbType.Int) {Value = dto.MovingUserId},
                                         new SqlParameter("@MovingContent", SqlDbType.VarChar)
                                             {Value = dto.MovingContent},
                                         new SqlParameter("@MovingTime", SqlDbType.DateTime) {Value = dto.MovingTime},
                                         new SqlParameter("@MovingType",SqlDbType.Int){Value = dto.MovingType}
                                     };
                if (dto.MovingId == 0)
                {
                    sql =
                        "insert into UserMoving(MovingUserId,MovingContent,MovingTime,MovingType) values(@MovingUserId,@MovingContent,@MovingTime,@MovingType)";
                }
                else
                {
                    sql =
                        "update UserMoving set MovingUserId=@MovingUserId,MovingContent=@MovingContent,MovingTime=@MovingTime,MovingType=@MovingType where MovingId=@MovingId";
                }
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Save(UserMovingDto dto)", ex);
                return false;
            }
        }

        /// <summary>
        /// 获取小组用户的动态
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public List<Hashtable> SelectMovingByGroup(int groupId)
        {
            try
            {
                var hts = new List<Hashtable>();
                const string sql =
                    "select * from UserMoving where MovingUserId in (select GmUserId from GroupMember where GmGroupId=@GmGroupId)";
                var param = new SqlParameter("@GmGroupId", SqlDbType.Int) { Value = groupId };
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, param))
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SelectMovingByGroup(int groupId)", ex);
                return null;
            }
        }

        public List<Hashtable> SlicedFindAll(int firstResult, int maxResult, int userId, string order)
        {
            try
            {
                var hts = new List<Hashtable>();
                order = !string.IsNullOrEmpty(order) ? order : "order by MovingId";
                var sql = "select row_number() over (" + order + ") as rowNumber, * from UserMoving";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (userId != 0)
                {
                    whereSql += " where MovingUserId=@MovingUserId";
                    parameters.Add(new SqlParameter("@MovingUserId", SqlDbType.Int) { Value = userId });
                }
                sql = string.Format(
                    "select * from (" + sql + whereSql + ") as UserMoving where rowNumber between {0} AND {1}",
                    firstResult, maxResult);
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()))
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SlicedFindAll(int firstResult, int maxResult, int userId, string order)", ex);
                return null;
            }
        }

        public int SelectCount(int userId)
        {
            try
            {
                const string sql = "select count(*) from UserMoving";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (userId != 0)
                {
                    whereSql += " where MovingUserId=@MovingUserId";
                    parameters.Add(new SqlParameter("@MovingUserId", SqlDbType.Int) { Value = userId });
                }
                var red = SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql + whereSql,
                                                  parameters.ToArray());
                return red == DBNull.Value ? 0 : Convert.ToInt32(red);
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SelectCount(int userId)", ex);
                return -1;
            }
        }
    }
}
